package util.io;


import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * Excel工具类
 *
 * @create ll
 * @createDate
 * @update
 * @updateDate
 */
public class PoiExcelReadUtil {
    private static final Logger log = LoggerFactory.getLogger(PoiExcelReadUtil.class);

    /**
     * 数字格式化
     */
    private static NumberFormat format = NumberFormat.getInstance();


    /**
     * 根据文件路径读取excel文件的指定sheet.
     * 会根据单元格的格式生成对应的类型，只有 String ,Boolean,Double,Date 四种类型。 还有可能会空。
     *
     * @param is          输入流
     * @param skipRows    需要跳过的行数   从0开始
     * @param skipColumns 需要跳过的列数   从0开始
     * @param readColumns 读取的列数
     * @param sheetNo     要读取的sheet的索引，从0开始
     * @return
     * @throws Exception
     */

    public static List<Object[]> readExcelAutoTransformType(InputStream is, int skipRows, int skipColumns, int readColumns, int sheetNo) throws Exception {

        log.info("【读取Excel】excelPath = {} ， skipRows = {} , skipColumns = {} , readColumns = {},sheetNo={}",
                is.toString(), skipRows, skipColumns, readColumns, sheetNo);

        if (null == is) {
            log.warn("【参数FileInputStream为空】");
            return new ArrayList();
        }


        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        List<Object[]> list = new ArrayList<Object[]>();
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        int rowNum = sheet.getPhysicalNumberOfRows(); // 得到总共的行数

        for (int i = skipRows; i < rowNum; i++) {
            Object[] rowValues = new Object[readColumns];

            HSSFRow row = sheet.getRow(i);

            for (int j = 0; j < readColumns; j++) {
                HSSFCell cell = row.getCell(j + skipColumns);
                rowValues[j] = getCellValue(cell);
            }

            list.add(rowValues);
        }

        wb.close();
        is.close();

        return list;
    }

    private static Object getCellValue(HSSFCell cell) {
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {

            return StringUtils.trimToNull(cell.getStringCellValue());

        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                return cell.getNumericCellValue();
            }

        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue();
        } else {
            return null;
        }

    }


    /**
     * 根据文件路径读取excel文件的指定sheet
     *
     * @param is          输入流
     * @param skipRows    需要跳过的行数   从0开始
     * @param skipColumns 需要跳过的列数   从0开始
     * @param readColumns 读取的列数
     * @param sheetNo     要读取的sheet的索引，从0开始
     * @return
     * @throws Exception
     */

    public static List<String[]> readExcel(InputStream is, int skipRows, int skipColumns, int readColumns, int sheetNo) throws Exception {

        log.info("【读取Excel】excelPath = {} ， skipRows = {} , skipColumns = {} , readColumns = {},sheetNo={}",
                is.toString(), skipRows, skipColumns, readColumns, sheetNo);

        if (null == is) {
            log.warn("【参数FileInputStream为空】");
            return new ArrayList<String[]>();
        }

        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        List<String[]> list = new ArrayList<String[]>();
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        int rowNum = sheet.getPhysicalNumberOfRows(); // 得到总共的行数,这里的行数是大于或等于录入数据的行数

        for (int i = skipRows; i < rowNum; i++) {
            String[] rowValues = new String[readColumns];
            boolean flag=false;

            HSSFRow row = sheet.getRow(i);
            if(row!=null){//该行不为空时进入
                for (int j = 0; j < readColumns; j++) {
                    HSSFCell cell = row.getCell(j + skipColumns);
                    rowValues[j] = getStringCellValue(cell);
                    if (!StringUtils.isEmpty(rowValues[j])){
                        flag=true;
                    }
                }
            }

            if (flag==false){//遇到一行全为空（空格）时,停止。
                break ;
            }


            list.add(rowValues);
        }


        wb.close();
        is.close();
        return list;
    }


    /**
     * 获取单元格数据内容为字符串类型的数据
     * 1、单元格数据内容（可能是布尔类型等，强制转换成String）
     * 2、空或空串将返回  null
     *
     * @param cell
     * @return
     */

    private static String getStringCellValue(HSSFCell cell) {
        if (cell == null) {
            return null;
        }
        String strCell = null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日");
                    strCell = simpleDateFormat.format(theDate);
                } else {
                    strCell = String.valueOf(format.format(cell.getNumericCellValue())).replace(",", "");
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue()).toUpperCase();
                break;
            default:
                break;
        }
        return StringUtils.trimToNull(strCell);
    }


    public static void main(String[] args) throws Exception {
        FileInputStream fileInputStream = new FileInputStream("c:/计划安排(20161115).xls");
        List<Object[]> strings = readExcelAutoTransformType(fileInputStream, 0, 0, 2, 0);


    }


}